/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbTool;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.text.ParseException;

/**
 *
 * @author Administrator
 */
public class functions {

    public ResultSet checklogin(String user, String pass) {
        try {
            String sqlSelect = "select * from staff where staffAcc=? and staffPass=?";
            Connection conn = null;
            PreparedStatement pstmt = null;
            myConnection cnn = new myConnection();
            ResultSet rs = null;
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);
            pstmt.setString(1, user);
            pstmt.setString(2, pass);
            rs = pstmt.executeQuery();
            return rs;
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.print(ex.getMessage());
            return null;
        }
        
    }

    public String getmd5(String txt) {

        MessageDigest mymd5 = null;
        try {
            mymd5 = MessageDigest.getInstance("MD5");
        } catch (NoSuchAlgorithmException ex) {
            System.out.println(ex);
        }
        mymd5.update(txt.getBytes());

        byte[] digest = mymd5.digest();
        StringBuffer hexStr = new StringBuffer();

        for (int i = 0; i < digest.length; i++) {
            txt = Integer.toHexString(0xFF & digest[i]);

            if (txt.length() < 2) {
                txt = "0" + txt;
            }

            hexStr.append(txt);
        }

        return hexStr.toString();
    }

    public int countStaff() {
        String sqlSelect = "select count(*) as count from staff";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        ResultSet rs = null;
        int count = 0;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return count;
    }

    public ResultSet viewStaff() {
        String sqlSelect1 = "SELECT * FROM Staff";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        ResultSet rs = null;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect1);
            rs = pstmt.executeQuery();

        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return rs;
    }

    public ResultSet viewStaffByID(int id) {
        String sqlSelect1 = "SELECT * FROM staff where id = " + id;
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        ResultSet rs = null;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect1);
            rs = pstmt.executeQuery();

        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return rs;
    }
    
    public ResultSet viewStaffByAcc(String acc) {
        String sqlSelect1 = "SELECT * FROM staff where staffAcc like '%" +acc+"%'";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        ResultSet rs = null;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect1);
            rs = pstmt.executeQuery();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return rs;
    }

    public ResultSet viewDealer() {
        String sqlSelect = "SELECT * FROM dealer";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        ResultSet rs = null;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);
            rs = pstmt.executeQuery();

        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return rs;
    }

    public int addStaff(String staffAcc, String dealerID, String fullName, int role, int gender, String birthday, String address, String phone, String mail, int status) throws ParseException {
        String sqlSelect = "INSERT INTO staff (staffAcc, staffPass,dealerID, fullName,role, gender, birthday, address, phone,mail, status) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        int i = 0;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);
            pstmt.setString(1, staffAcc);
            pstmt.setString(2, "123456");
            pstmt.setString(3, dealerID);
            pstmt.setString(4, fullName);
            pstmt.setInt(5, role);
            if (gender == 1) {
                pstmt.setBoolean(6, true);
            } else {
                pstmt.setBoolean(6, false);
            }
            pstmt.setString(7, birthday);
            pstmt.setString(8, address);
            pstmt.setString(9, phone);
            pstmt.setString(10, mail);
            if (status == 1) {
                pstmt.setBoolean(11, true);
            } else {
                pstmt.setBoolean(11, false);
            }
            i = pstmt.executeUpdate();
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return i;
    }

    public int updateStaff(String dealerID, String fullName, int role, int gender, String birthday, String address, String phone, String mail, int status,int id) throws ParseException {
        String sqlSelect = "UPDATE staff set dealerID=?, fullName=?,role=?, gender=?, birthday=?, address=?, phone=?,mail=?, status=? where id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        int i=0;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);            
            pstmt.setString(1, dealerID);
            pstmt.setString(2, fullName);
            pstmt.setInt(3, role);
            if (gender == 1) {
                pstmt.setBoolean(4, true);
            } else {
                pstmt.setBoolean(4, false);
            }
            pstmt.setString(5, birthday);
            pstmt.setString(6, address);
            pstmt.setString(7, phone);
            pstmt.setString(8, mail);
            if (status == 1) {
                pstmt.setBoolean(9, true);
            } else {
                pstmt.setBoolean(9, false);
            }
            pstmt.setInt(10, id);
            i = pstmt.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return i;
    }
    public int resetPass(int id) throws ParseException {
        String pass = "123456";
        String sqlSelect = "UPDATE staff set staffPass=? where id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        myConnection cnn = new myConnection();
        int i=0;
        try {
            conn = cnn.connect();
            pstmt = conn.prepareStatement(sqlSelect);                        
            pstmt.setString(1, pass);
            pstmt.setInt(2, id);
            i = pstmt.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex);
        }
        return i;
    }
}
